package com.bdqn.oa.dao.impl;

import com.bdqn.oa.dao.BusinessTripDao;

import com.bdqn.oa.entity.BusinessTrip;
import com.bdqn.oa.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.SQLException;
import java.util.List;

public class BusinessTripDaoImpl implements BusinessTripDao {
    QueryRunner queryRunner = new QueryRunner();
    /**
     * 添加出差
     * businessTrip对象里面传`employee_id`,`content`,`datetime`,`start_time`,`end_time`
     * @param businessTrip
     * @return
     */
    @Override
    public int insertBusinesstrip(BusinessTrip businessTrip)throws SQLException {
        String sql = "insert into business_trip(`employee_id`,`content`,`datetime`,`start_time`,`end_time`)VALUE(?,?,?,?,?);";
        Object[] params = {
                businessTrip.getEmployeeId(),businessTrip.getComment(),businessTrip.getDatetime(),businessTrip.getStartTime(), businessTrip.getEndTime()
        };
        return queryRunner.update(DruidUtils.getConnection(),sql,params);
    }

    /**
     * 查询所有的出差信息    查询的结果通过申请时间降序排列
     *
     * @return
     */
    @Override
    public List<BusinessTrip> listQueryAlltrip() throws SQLException{
        String sql = "select `id`,`employee_id` as employeeid,`content`,`datetime`,`start_time` as starttime,`end_time` as endtime,`status`,`comment`,`comment_name` as commentname," +
                "`comment_datetime` as commentdatetime  from  business_trip order by datetime desc;";
        return queryRunner.query(DruidUtils.getConnection(),sql,new BeanListHandler<BusinessTrip>(BusinessTrip.class));
    }

    /**
     * 通过员工ID查询所有出差信息
     *
     * @param employeeId
     * @return
     */
    @Override
    public List<BusinessTrip> listQueryByEmployeeId(Integer employeeId)throws SQLException {
        String sql = "select `id`,`employee_id` as employeeid ,`content`,`datetime`,`start_time` as starttime,`end_time` as endtime,`status`,`comment`,`comment_name` as  commentname,`comment_datetime` as commentdatetime from  business_trip where employee_id = ? order by datetime desc;";
        return queryRunner.query(DruidUtils.getConnection(),sql,new BeanListHandler<BusinessTrip>(BusinessTrip.class),employeeId);
    }

    /**
     * 通过批注人查询所有的出差信息
     *
     * @param commentName
     * @return
     */
    @Override
    public List<BusinessTrip> listQueryByCommentName(String commentName)throws SQLException {
        String sql = "select `id`,`employee_id` as employeeid ,`content`,`datetime`,`start_time` as starttime,`end_time` as endtime,`status`,`comment`,`comment_name` as  commentname,`comment_datetime` as commentdatetime from  business_trip where comment_name = ? order by datetime desc;";
        return  queryRunner.query(DruidUtils.getConnection(),sql,new BeanListHandler<BusinessTrip>(BusinessTrip.class),commentName);
    }

    /**
     * 通过批注状态查询出差信息
     *
     * @param status
     * @return
     */
    @Override
    public List<BusinessTrip> listQueryByStatus(Integer status)throws SQLException {
       String sql = "select  `id`,`employee_id` as employeeid ,`content`,`datetime`,`start_time` as starttime,`end_time` as endtime,`status`,`comment`,`comment_name` as  commentname,`comment_datetime` as commentdatetime from  business_trip where status = ? order by datetime desc;";
        return  queryRunner.query(DruidUtils.getConnection(),sql,new BeanListHandler<BusinessTrip>(BusinessTrip.class),status);
    }

    /**
     *  上级批注 （, BusinessTrip类中id,status,comment,commentName,commentDatetime）
     *
     *
     * @param businessTrip
     * @return
     * @throws SQLException
     */
    @Override
    public int updateComment( BusinessTrip businessTrip)throws SQLException {
        String sql = "update business_trip set status = ?,comment = ?,comment_name = ?,comment_datetime =? where id = ?;";
        Object[] params = {
               businessTrip.getStatus(),businessTrip.getComment(),businessTrip.getCommentName(),businessTrip.getCommentDatetime(),businessTrip.getId()
        };
        return  queryRunner.update(DruidUtils.getConnection(),sql,params);
    }
}
